# -*- coding: utf-8 -*-
# telecom_visualization.py
import pandas as pd 
import matplotlib.pyplot as plt 
import seaborn as sns 
from pyhive import hive 
import os
# 设置中文字体支持
plt.rcParams["font.family"] = ["SimHei", "WenQuanYi Micro Hei", "Heiti TC"] 
plt.rcParams["axes.unicode_minus"] = False # 解决负号显示问题
# 连接Hive
def connect_hive():
    try:
        conn = hive.Connection(
            host="node1", # HiveServer2主机名
            port=10001, # 默认端口
            username="hive",
            database="default"
        )
        print("Hive连接成功！")
        return conn
    except Exception as e:
        print("Hive连接失败: {}".format(e)) # 修改为format()
        return None
# 获取通话时长统计数据
def get_call_stats(conn):
    cursor = conn.cursor()
    
    # 查询每日通话统计
    daily_query = """
    SELECT
        SUBSTR(call_time, 1, 10) AS call_date,
        COUNT(*) AS call_count,
        SUM(duration) AS total_duration
    FROM telecom_call_records_hive
    GROUP BY SUBSTR(call_time, 1, 10)
    ORDER BY call_date
    """
    cursor.execute(daily_query)
    daily_stats = cursor.fetchall()
    daily_df = pd.DataFrame(daily_stats, columns=['日期', '通话次数', '总时长(秒)'])
    
    # 查询每月通话统计
    monthly_query = """
    SELECT
        SUBSTR(call_time, 1, 7) AS call_month,
        COUNT(*) AS call_count,
        SUM(duration) AS total_duration
    FROM telecom_call_records_hive
    GROUP BY SUBSTR(call_time, 1, 7)
    ORDER BY call_month
    """
    cursor.execute(monthly_query)
    monthly_stats = cursor.fetchall()
    monthly_df = pd.DataFrame(monthly_stats, columns=['月份', '通话次数', 
'总时长(秒)'])
    
    # 查询用户通话时长排名
    user_query = """
    SELECT
        caller AS user,
        COUNT(*) AS call_count,
        SUM(duration) AS total_duration
    FROM telecom_call_records_hive
    GROUP BY caller
    ORDER BY total_duration DESC
    LIMIT 10
    """
    cursor.execute(user_query)
    user_stats = cursor.fetchall()
    user_df = pd.DataFrame(user_stats, columns=['用户', '通话次数', '总时长(秒)'])
    
    return daily_df, monthly_df, user_df
# 生成可视化图表
def generate_plots(daily_df, monthly_df, user_df):
    # 创建保存图表的目录
    if not os.path.exists('charts'):
        os.makedirs('charts')
    
    # 1. 每日通话次数和时长趋势图
    plt.figure(figsize=(14, 6))
    
    plt.subplot(1, 2, 1)
    sns.lineplot(x='日期', y='通话次数', data=daily_df)
    plt.title('每日通话次数趋势')
    plt.xticks(rotation=45)
    plt.tight_layout()
    
    plt.subplot(1, 2, 2)
    sns.lineplot(x='日期', y='总时长(秒)', data=daily_df)
    plt.title('每日通话总时长趋势')
    plt.xticks(rotation=45)
    plt.tight_layout()
    
    plt.savefig('charts/daily_trends.png')
    plt.close()
    
    # 2. 每月通话次数和时长分布图
    plt.figure(figsize=(14, 6))
    
    plt.subplot(1, 2, 1)
    sns.barplot(x='月份', y='通话次数', data=monthly_df)
    plt.title('每月通话次数分布')
    plt.xticks(rotation=45)
    plt.tight_layout()
    
    plt.subplot(1, 2, 2)
    sns.barplot(x='月份', y='总时长(秒)', data=monthly_df)
    plt.title('每月通话总时长分布')
    plt.xticks(rotation=45)
    plt.tight_layout()
    
    plt.savefig('charts/monthly_distribution.png')
    plt.close()
    
    # 3. 通话时长TOP 10用户
    plt.figure(figsize=(12, 6))
    sns.barplot(x='总时长(秒)', y='用户', data=user_df)
    plt.title('通话时长排名前十的用户')
    plt.tight_layout()
    
    plt.savefig('charts/top_users.png')
    plt.close()
    
    print("图表生成完成！已保存到 charts 目录下。")
# 主函数
def main():
    conn = connect_hive()
    if conn:
        daily_df, monthly_df, user_df = get_call_stats(conn)
        generate_plots(daily_df, monthly_df, user_df)
        conn.close()
if __name__ == "__main__":
    main()
